package com.wframe.project.jnd.dao;

import java.math.BigDecimal;
import java.util.*;

import com.wframe.project.jnd.vo.CartItem;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSON;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.frame.util.DateConvert;
import com.wframe.framework.database.BaseDao;
import com.wframe.framework.database.ParmSqlVo;
import com.wframe.framework.util.ConversionUtil;
import com.wframe.project.former.entity.ProductsAttrvalBean;
import com.wframe.project.former.enums.OrderStatusEnum;

@Repository
public class OrderDao extends BaseDao {
    @Autowired
    private PromotionDao promotionDao;


    public List<JdataBean> queryFaretemp() {
        return querySql("select f.*,a.province,a.city from wb_faretemp f left join wb_faretemp_area a on f.id=a.tempid", null);
    }

    public void addToCart(String userid, String productid, String priceid, int amount) throws Exception {
        JdataBean getb = getCartByPriceid(userid, priceid);
        if (getb != null) {
            execute("update wb_shopcart set num=num+" + amount + ",intime=now() where id=?", new Object[]{getb.getString("id")});
        } else {
            JdataBean bean = new JdataBean();
            bean.put("userid", userid);
            bean.put("productid", productid);
            bean.put("priceid", priceid);
            bean.put("num", amount);
            bean.put("intime", new Date());
            bean.put("create_time", new Date());
            save("wb_shopcart", bean.getDataMap());
        }
    }

    public void delCart(String userid, String cartid) {
        execute("delete from wb_shopcart where userid=? and id=?", new Object[]{userid, cartid});
    }

    public void updateCartCount(String userid, String cartid, Integer count) {
        execute("update wb_shopcart set num = ? where userid=? and id=?", new Object[]{count, userid, cartid});
    }

    public JdataBean getCartByPriceid(String userid, String priceid) {
        return getObject("select * from wb_shopcart where userid=? and priceid=?", new Object[]{userid, priceid});
    }

    public List<Map> queryCart(String userid) {
        String sql = "select c.id cartid,c.num,t.id,t.productid,t.name,t.attrcodes,t.price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.code productcode,p.name productname,p.unitname,p.attrvals,p.iscontrol,p.status,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_shopcart c"
                + " inner join wb_products_prices t on t.id=c.priceid"
                + " inner join wb_products p on p.id=c.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where p.status<>-1 and c.userid=? order by c.intime desc";
        List<Map> productlist = querySqlMap(sql, new Object[]{userid});
        for (Map product : productlist) {
            String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
            if (StringUtils.isBlank(photo))
                photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
            product.put("photo", photo);
            if (Integer.parseInt(product.get("num").toString()) > Integer.parseInt(product.get("amount").toString()))
                product.put("num", product.get("amount"));
            String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(OrderDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                }
            }
        }
        return productlist;
    }

    public List<JdataBean> queryCartProduct(String userid, String[] cartids) {
        ParmSqlVo sqlvo = ConversionUtil.arrayToInSql(cartids);
        sqlvo.getParms().add(userid);
        String sql = "select c.id cartid,c.productid,c.priceid,p.name productname,r.name attrname,r.price,r.attrcodes from wb_shopcart c,wb_products p,wb_products_prices r where c.productid=p.id and c.priceid=r.id";
        sql += " and c.id in (" + sqlvo.getInSql() + ") and c.userid=?";
        return querySql2(sql, sqlvo.getParms());
    }

    public List<JdataBean> queryProductBypriceid(String[] priceids) {
        ParmSqlVo sqlvo = ConversionUtil.arrayToInSql(priceids);
        String sql = "select r.id priceid,r.productid,p.name productname,p.status,r.name attrname,r.price,r.attrcodes,(ifnull(r.amount,0)-ifnull(r.djamount,0)) amount from wb_products p,wb_products_prices r where r.productid=p.id";
        sql += " and r.id in (" + sqlvo.getInSql() + ")";
        return querySql2(sql, sqlvo.getParms());
    }

    public void saveOrder(JdataBean order, List<JdataBean> products) throws Exception {
        String orderid = saveReturnKey("wb_order_list", order.getDataMap());
        for (JdataBean product : products) {
            JdataBean data = new JdataBean();
            data.put("orderid", orderid);
            data.put("productid", product.getString("productid"));
            data.put("priceid", product.getString("priceid"));
            data.put("amount", product.getString("amount"));
            data.put("price", product.getString("price"));
            data.put("productname", product.getString("productname"));
            data.put("attrnames", product.getString("attrnames"));
            data.put("gift", product.getInt("gift"));
            data.put("gift_type", product.getInteger("gift_type"));
            save("wb_order_product", data.getDataMap());
        }
    }

    public void updateOrder(JdataBean order) throws Exception {
        Map key = new HashMap();
        key.put("id", order.getString("id"));
        order.remove("id");
        update("wb_order_list", order.getDataMap(), key);
    }

    public void updateBycode(JdataBean order) throws Exception {
        Map key = new HashMap();
        key.put("code", order.getString("code"));
        order.remove("id");
        order.remove("code");
        update("wb_order_list", order.getDataMap(), key);
    }

    public void payOk(String paycode) {
        execute("update wb_order_list set ispay=1 where paycode=?", new Object[]{paycode});
        updateStatusByPayCode(paycode, 3);
    }

    public void updateStatusByPayCode(String paycode, int status) {
        execute("update wb_order_list set status=? where paycode=?", new Object[]{status, paycode});
    }

    public void updateSynByPayCode(String paycode, int issyn) {
        execute("update wb_order_list set issyn=? where paycode=?", new Object[]{issyn, paycode});
    }

    public void delCarts(String userid, String... cartid) {
        ParmSqlVo sqlvo = ConversionUtil.arrayToInSql(cartid);
        sqlvo.getParms().add(userid);
        execute2("delete from wb_shopcart where id in (" + sqlvo.getInSql() + ") and userid=?", sqlvo.getParms());
    }

    public JdataBean getOrderByCode(String ordercode) {
        return getObject("select l.*,DATE_FORMAT(from_unixtime(order_time),'%Y-%m-%d %H:%i:%s') ordertime,DATE_FORMAT(from_unixtime(send_time),'%Y-%m-%d %H:%i:%s') sendtime,DATE_FORMAT(from_unixtime(receipt_time),'%Y-%m-%d %H:%i:%s') receipttime from wb_order_list l where code=?", new Object[]{ordercode});
    }

    public JdataBean getOrder(String orderid) {
        return getObject("select l.*,DATE_FORMAT(from_unixtime(order_time),'%Y-%m-%d %h:%i:%s') ordertime from wb_order_list l where id=?", new Object[]{orderid});
    }

    public String getProductMoneyOffTypeById(String productid) throws Exception {
        JdataBean class_id = getObject("select id,pid from wb_products_class where code =(select classcode from wb_products_and_class where productid=?)", new Object[]{productid});
        String classid = "";
        String classpid = "";
        if (class_id != null) {
            classid = class_id.getString("id");
            classpid = class_id.getString("pid");
        }
        JdataBean jdataBean = getObject("SELECT GROUP_CONCAT(case promotion_type when '0' THEN '0'  when '1' THEN '1' when '2' THEN '2' when '3' THEN '3' end) moneytypes from wb_promotions WHERE state=1 AND end_time>now() and start_time<now() and ( product_type=0 or (product_type=1 and product_val =?) or (product_type=2 and product_val in (SELECT promotionid from wb_promotion_product WHERE productid=?)) or (product_type=3 and product_val in (?,?,?)))", new Object[]{productid, productid, 1, classid, classpid});
        if (jdataBean == null) {
            return null;
        }
        String moneyoffType = jdataBean.getString("moneytypes");
        return moneyoffType;
    }


    public PageResult queryOrderProduct(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "SELECT SUM(c.amount) buyamount,ol.order_time AS order_time,c.price buyprice,c.price * c.amount sumprice,t.*, p. CODE productcode,p. NAME productname,p.unitname,p.iscontrol,p. STATUS,p.attrvals,IFNULL(pmfs.price,0) AS pg_price,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl, sm.viewpath product_photoview " +
                " FROM wb_order_product c " +
                " INNER JOIN wb_products_prices t ON t.id = c.priceid " +
                " LEFT JOIN wb_order_list ol  ON ol.id = c.orderid " +
                " LEFT JOIN ( SELECT pm.product_val,fs.price FROM wb_promotions pm LEFT JOIN wb_promotion_flash_sale fs ON fs.promotionid = pm.id WHERE pm.promotion_type = 0 AND pm.state = 1 AND pm.platform in (0,?) AND start_time < NOW() AND end_time > now() ) pmfs ON pmfs.product_val = c.priceid " +
                " LEFT JOIN ( SELECT * FROM wb_products_pricegrade WHERE grade = 11 ) pg ON pg.productid = c.productid " +
                " INNER JOIN wb_products p ON p.id = c.productid " +
                " LEFT JOIN app_pictures s ON s.id = t.photo " +
                " LEFT JOIN app_pictures sm ON sm.id = p.photo " +
                " WHERE p. STATUS <>- 1 ";

        List parmlist = new ArrayList();
        parmlist.add(parms.getInt("platform"));
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("status"))) {
                sql += " and c.orderid IN ( SELECT id FROM wb_order_list WHERE status >=? ) AND c.gift = 0 ";
                parmlist.add(parms.getString("status"));
            }
            if (StringUtils.isNotBlank(parms.getString("userid"))) {
                sql += " and c.orderid in (select id from wb_order_list where userid=?)";
                parmlist.add(parms.getString("userid"));
            }
            if (StringUtils.isNotBlank(parms.getString("start"))) {
                sql += " and ol.order_time >= ?";
                parmlist.add(DateConvert.toDate(parms.getString("start")).getTime() / 1000);
            }
            if (StringUtils.isNotBlank(parms.getString("end"))) {
                sql += " and ol.order_time <= ?";
                parmlist.add(DateConvert.toDate(parms.getString("end") + " 23:59:59").getTime() / 1000);
            }

        }
        sql += " GROUP BY c.productid ORDER BY buyamount desc";
        PageResult pr = querySql2(sql, parmlist, size, pageno);
        return pr;
    }

    public List<Map> queryOrderProduct(String ordercode) {
        String sql = "select c.times,c.promotionid as promotionid , c.gift gift,c.amount buyamount,c.price buyprice,c.price*c.amount sumprice,t.id,t.productid,t.name,t.attrcodes,t.price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.code productcode,p.name productname,p.unitname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_order_product c"
                + " inner join wb_products_prices t on t.id=c.priceid"
                + " inner join wb_products p on p.id=c.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where  c.orderid in (select id from wb_order_list where code=?)";
        List<Map> productlist = querySqlMap(sql, new Object[]{ordercode});
        for (Map product : productlist) {
            String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
            if (StringUtils.isBlank(photo))
                photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
            product.put("photo", photo);
            String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(OrderDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                }
            }
        }
        return productlist;
    }

    public int queryOrderProductTotalAmount(String ordercode) {
        JdataBean productBean = getObject("select SUM(op.amount) total from wb_order_product op INNER  JOIN wb_order_list ol ON op.orderid= ol.id where ol.`CODE` = ? ", new Object[]{ordercode});
        int amount = productBean.getInt("total");
        return amount;
    }

    public List<JdataBean> getOrderProduct(String orderid) {
        return querySql("select * from wb_order_product where orderid=?", new Object[]{orderid});
    }

    public PageResult queryOrders(JdataBean parms, int size, int pno) {
        String sql = "select l.* from wb_order_list l where status>0";
        List parmlist = new ArrayList();
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("userid"))) {
                sql += " and l.userid=?";
                parmlist.add(parms.getString("userid"));
            }
            if (StringUtils.isNotBlank(parms.getString("ordercode"))) {
                sql += " and l.code like ?";
                parmlist.add("%" + parms.getString("ordercode") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("queryList"))) {
                sql += " and (l.`CODE` LIKE ? or l.id in (select r.orderid from wb_order_product r,wb_products p where r.productid=p.id and (p.name like ? or p.code like ?)))";
                parmlist.add("%" + parms.getString("queryList") + "%");
                parmlist.add("%" + parms.getString("queryList") + "%");
                parmlist.add("%" + parms.getString("queryList") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("status"))) {
                sql += " and l.status=?";
                parmlist.add(parms.getString("status"));
            }
            if (StringUtils.isNotBlank(parms.getString("backtag"))) {
                sql += " and l.backtag=?";
                parmlist.add(parms.getString("backtag"));
            }
            if (StringUtils.isNotBlank(parms.getString("inbacktag"))) {
                sql += " and l.backtag in (" + parms.getString("inbacktag") + ")";
            }
            if (StringUtils.isNotBlank(parms.getString("start"))) {
                sql += " and l.order_time >= ?";
                parmlist.add(DateConvert.toDate(parms.getString("start")).getTime() / 1000);
            }
            if (StringUtils.isNotBlank(parms.getString("end"))) {
                sql += " and l.order_time <= ?";
                parmlist.add(DateConvert.toDate(parms.getString("end") + " 23:59:59").getTime() / 1000);
            }
        }
        sql += " order by order_time desc";
        return querySqlMap2(sql, parmlist, size, pno);
    }

    public String productNumber(String orderid) throws Exception {

        JdataBean jdataBean = getObject("select COUNT(id) AS product_number from wb_order_product where orderid =?", new Object[]{orderid});
        String pnumber = jdataBean.getString("product_number");
        return pnumber;
    }

    public List<JdataBean> queryStatusCount(String userid) {
        String sql = "select count(userid) statussum , status from wb_order_list where userid = ? GROUP BY status";
        return querySql(sql, new Object[]{userid});
    }

    /**
     * 收货（收货后订单直接完成）
     *
     * @param userid
     * @param ordercode
     */
    public void receiptGoods(String userid, String ordercode) {
        execute("update wb_order_list set receipt_time=?,status=?,commenttag=1,integral_state=1 where code=? and userid=?", new Object[]{new Date().getTime() / 1000, OrderStatusEnum.End.getStatus(), ordercode, userid});
    }

    public String backorder(JdataBean bean) throws Exception {
        execute("delete from wb_order_backproduct where backid in (select id from wb_order_back where orderid=?)", new Object[]{bean.getString("orderid")});
        execute("delete from wb_order_back where orderid=?", new Object[]{bean.getString("orderid")});
        return saveReturnKey("wb_order_back", bean.getDataMap());
    }

    public void backorder_product(String orderid, String backid, List<JdataBean> products) throws Exception {
        for (JdataBean product : products) {
            product.put("orderid", orderid);
            product.put("backid", backid);
            save("wb_order_backproduct", product.getDataMap());
        }
    }

    public List<Map> queryBackOrderProduct(String orderid) {
        String sql = "select t.id,c.amount buyamount,c.price buyprice,c.price*c.amount sumprice,c.attrnames,c.productname,p.code productcode,p.unitname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_order_backproduct c"
                + " inner join wb_products_prices t on t.id=c.priceid"
                + " inner join wb_products p on p.id=c.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where c.backid in (select id from wb_order_back where orderid=?)";
        List<Map> productlist = querySqlMap(sql, new Object[]{orderid});
        for (Map product : productlist) {
            String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
            if (StringUtils.isBlank(photo))
                photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
            product.put("photo", photo);
            String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(OrderDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                }
            }
        }
        return productlist;
    }

    public void updateBacktag(String orderid, int backtag) {
        execute("update wb_order_list set backtag=? where id=?", new Object[]{backtag, orderid});
    }

    public JdataBean getBackOrder(String orderid) {
        return getObject("select l.* from wb_order_back l where orderid=? ", new Object[]{orderid});
    }

    public void cancelorder(String ordercode) {
        execute("update wb_order_list set status=? where code=?", new Object[]{OrderStatusEnum.Cancel.getStatus(), ordercode});
    }

    public void cancelbackorder(String orderid) {
        execute("update wb_order_back set status=4 where orderid=?", new Object[]{orderid});
        execute("update wb_order_list set backtag=4 where id=?", new Object[]{orderid});
    }

    /**
     * 常购清单管理
     */
    public PageResult queryOften(JdataBean parms, int size, int pageno) {
        String sql = "select c.id oftenid,c.amount buyamount,t.id,t.productid,t.name,t.attrcodes,CASE  WHEN pp.price>0 THEN pp.price ELSE t.price END as price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.code productcode,p.name productname,p.unitname,p.attrvals,p.status,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_user_often c"
                + " inner join wb_products_prices t on t.id=c.priceid"
                + " inner join wb_products p on p.id=c.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " join wbuser u on c.userid=u.id"
                + " left join wb_products_pricegrade pp on c.productid=pp.productid and u.pricegrade=pp.grade"
                + " where p.status<>-1";
        List parmlist = new ArrayList();
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("like"))) {
                sql += " and (p.name like ? or t.name like ? or p.acronymname like ? or p.id in (select b.productid from wb_products_attr a,wb_products_attrval b where a.id=b.attrid and a.code='B_chanjia' and b.val like ?))";
                parmlist.add("%" + parms.getString("like") + "%");
                parmlist.add("%" + parms.getString("like") + "%");
                parmlist.add("%" + parms.getString("like") + "%");
                parmlist.add("%" + parms.getString("like") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("userid"))) {
                sql += " and c.userid=?";
                parmlist.add(parms.getString("userid"));
            }
            if ("true".equals(parms.getString("canbuy"))) {
                sql += " and t.amount>0 and p.status=2";
            }
        }
        sql += " order by p.status,c.id";
        PageResult pr = null;
        if (size == -1) {
            List<Map> list = querySqlMap2(sql, parmlist);
            pr = new PageResult(list.size(), 1);
            pr.setList(list);
        } else {
            pr = querySqlMap2(sql, parmlist, size, pageno);
        }
        List<Map> productlist = pr.getList();
        if (!"false".equals(parms.getString("queryinfo"))) {
            for (Map product : productlist) {
                String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
                if (StringUtils.isBlank(photo))
                    photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
                product.put("photo", photo);
                String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
                if (StringUtils.isNotBlank(attrjson)) {
                    //将商品属性取出
                    try {
                        List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                        for (ProductsAttrvalBean attrbean : jsonarr) {
                            product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                        }
                    } catch (Exception ew) {
                        Logger.getLogger(OrderDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                    }
                }
            }
        }
        return pr;
    }

    public void delOften(String userid, String priceid) {
        execute("delete from wb_user_often where userid=? and priceid=?", new Object[]{userid, priceid});
    }

    public JdataBean getOften(String userid, String priceid) {
        return getObject("select * from wb_user_often where userid=? and priceid=?", new Object[]{userid, priceid});
    }

    public void addOften(String userid, String productid, String priceid) throws Exception {
        JdataBean getb = getOften(userid, priceid);
        if (getb != null) {
//			execute("update wb_user_often set num=num+"+amount+",intime=now() where id=?", new Object[]{getb.getString("id")});
        } else {
            JdataBean bean = new JdataBean();
            bean.put("userid", userid);
            bean.put("productid", productid);
            bean.put("priceid", priceid);
            save("wb_user_often", bean.getDataMap());
        }
    }

    public List<JdataBean> statStatusNum(String userid) throws Exception {
        return querySql("select status,count(id) num from wb_order_list where userid=? group by status", new Object[]{userid});
    }

    public int statCartnum(String userid) throws Exception {
        JdataBean data = getObject("SELECT " +
                " COUNT(*) num " +
                "FROM " +
                " wb_shopcart ws " +
                "JOIN wb_products wp ON ws.productid = wp.id " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.productid " +
                " FROM " +
                "  wb_products_controlsales wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   comp.province, " +
                "   comp.city, " +
                "   comp.region " +
                "  FROM " +
                "   wbuser wu " +
                "  JOIN wb_company comp ON wu.id = comp.userid " +
                "  WHERE " +
                "   wu.id = ? " +
                "  AND wu.state = 1 " +
                "  AND wu.authstate = 2 " +
                "  AND comp.state = 2 " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wpc.val = wat.id " +
                " WHERE " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                " OR ( " +
                "  wpc.type = 2 " +
                "  AND wpc.val = u_pricegrade " +
                " ) " +
                " OR ( " +
                "  wpc.type = 3 " +
                "  AND ( " +
                "   ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wat.city, '') = '' " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wuc.region, '') != '' " +
                "    AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "   ) " +
                "  ) " +
                " ) " +
                " GROUP BY " +
                "  wpc.productid " +
                ") wpc ON ws.productid = wpc.productid " +
                "WHERE " +
                " ws.userid = ? " +
                "AND wp.`status` = 2 " +
                "AND ( " +
                " wp.iscontrol = 0 " +
                " OR ( " +
                "  wp.iscontrol = 1 " +
                "  AND wpc.productid IS NOT NULL " +
                " ) " +
                ")", new Object[]{userid, userid});
        if (data != null) return data.getInt("num");
        return 0;
    }

    public void receiptchecktask(int day) {
        execute("update wb_order_list set status=?,receipt_time=? where TIMESTAMPDIFF(day,from_unixtime(send_time),now())>? and status=? and deltag=0 and send_time is not null", new Object[]{OrderStatusEnum.End.getStatus(), new Date().getTime() / 1000, day, OrderStatusEnum.Sended.getStatus()});
    }

    /**
     * 冻结库存
     */
    public void freezeStock(String priceid, int amount) {
        execute("update wb_products_prices set djamount=ifnull(djamount,0)+" + amount + ",salesnum=salesnum+" + amount + " where id=?", new Object[]{priceid});
    }

    /**
     * 释放冻结库存
     */
    public void releaseStock(String priceid, int amount) {
        execute("update wb_products_prices set djamount=ifnull(djamount,0)-" + amount + ",salesnum=salesnum-" + amount + " where id=?", new Object[]{priceid});
        execute("update wb_products_prices set djamount=0 where djamount<0", null);
    }

    public void updateStatus(int status, int syn) {
        String sql = "update wb_order_list set status=?,issyn=? WHERE issyn!=2 and payCode in (SELECT payCode from (SELECT DISTINCT(paycode) payCode from wb_order_list WHERE status=" + OrderStatusEnum.Confirm.getStatus() + " and order_time<? and EXISTS(SELECT 1 from wb_sys_pro WHERE code='orderswitch' and val=0)) t)";
        operate(sql, new Object[]{status, syn, new Date().getTime() / 1000 - 300});
    }


    public List<CartItem> findOrderItem(String ordercode) {
        String sql = "SELECT m.amount count,m.productid id from wb_order_product m join wb_order_list n on m.orderid=n.id WHERE n.code=?";
        return list(sql, new Object[]{ordercode}, CartItem.class);
    }

    public void add(Map map) {
        insert("wb_payment", map);
    }

    public void updateStatusCode(String code, String payway, String paysn) {
        execute("update wb_order_list set ispay=1 , status=?, pay_time=? , pay_way=? , paycode=? where code=?", new Object[]{OrderStatusEnum.Confirm.getStatus(), new Date().getTime() / 1000, payway, paysn, code});
    }

    public JdataBean findOrderTips(String code) {
        String sql = "SELECT m.receipt_shen,m.receipt_shi,m.receipt_qu,m.receipt_address,m.receipt_user,m.receipt_phone,m.pay_money,m.code,m.status,m.paytype,m.ispay,m.paycode,n.productname"
                + " from wb_order_list m"
                + " JOIN (SELECT GROUP_CONCAT(productname) productname,orderid from wb_order_product WHERE orderid=(SELECT id from wb_order_list WHERE code=?))n on m.id=n.orderid";
        return uniqueResult(sql, code);
    }

    public void updateConfirmByCode(String code) {
        String sql = "update wb_order_list set paytype=?,status=? WHERE status=1 and paytype!=2 and ispay=0 and pay_money>0 and code=?";
        operate(sql, new Object[]{2, OrderStatusEnum.Confirm.getStatus(), code});
    }

    public List<CartItem> findCartProducts(String userid) {
        String sql = "SELECT " +
                " ws.id, " +
                " ws.productid, " +
                " ws.num count, " +
                " IFNULL(wpp.amount, 0) - IFNULL(wpp.djamount, 0) - IFNULL(ws.num, 0) amount " +
                "FROM " +
                " wb_shopcart ws " +
                "JOIN wb_products_prices wpp ON ws.productid = wpp.productid " +
                "JOIN wb_products wp ON ws.productid = wp.id " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.productid " +
                " FROM " +
                "  wb_products_controlsales wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   comp.province, " +
                "   comp.city, " +
                "   comp.region " +
                "  FROM " +
                "   wbuser wu " +
                "  JOIN wb_company comp ON wu.id = comp.userid " +
                "  WHERE " +
                "   wu.id = ? " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wpc.val = wat.id " +
                " WHERE " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                " OR ( " +
                "  wpc.type = 2 " +
                "  AND wpc.val = u_pricegrade " +
                " ) " +
                " OR ( " +
                "  wpc.type = 3 " +
                "  AND ( " +
                "   ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wat.city, '') = '' " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wuc.region, '') != '' " +
                "    AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "   ) " +
                "  ) " +
                " ) " +
                ") wpc ON ws.productid = wpc.productid " +
                "WHERE " +
                " ws.userid = ? " +
                "AND wp.`status` = 2 " +
                "AND ( " +
                " wp.iscontrol = 0 " +
                " OR ( " +
                "  wp.iscontrol = 1 " +
                "  AND ws.productid = wpc.productid " +
                " ) " +
                ") " +
                "ORDER BY " +
                " ws.id DESC";
        return list(sql, new Object[]{userid, userid}, CartItem.class);
    }

    public String saveOrder(JdataBean order) throws Exception {
        return saveReturnKey("wb_order_list", order.getDataMap());
    }

    public List<CartItem> findCartItems(String userId, String[] ids) {
        String sqlvo = StringUtils.join(ids, ",");
        String sql = "SELECT " +
                " ws.id, " +
                " ws.productid, " +
                " ws.num count, " +
                " IFNULL(wpp.amount, 0) - IFNULL(wpp.djamount, 0) - IFNULL(ws.num, 0) amount " +
                "FROM " +
                " wb_shopcart ws " +
                "JOIN wb_products_prices wpp ON ws.productid = wpp.productid " +
                "JOIN wb_products wp ON ws.productid = wp.id " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.productid " +
                " FROM " +
                "  wb_products_controlsales wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   comp.province, " +
                "   comp.city, " +
                "   comp.region " +
                "  FROM " +
                "   wbuser wu " +
                "  JOIN wb_company comp ON wu.id = comp.userid " +
                "  WHERE " +
                "   wu.id = ? " +
                "  AND wu.state = 1 " +
                "  AND wu.authstate = 2 " +
                "  AND comp.state = 2 " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wpc.val = wat.id " +
                " WHERE " +
                "  wpc.productid IN (" + sqlvo + ") " +
                " AND ( " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                "  OR ( " +
                "   wpc.type = 2 " +
                "   AND wpc.val = u_pricegrade " +
                "  ) " +
                "  OR ( " +
                "   wpc.type = 3 " +
                "   AND ( " +
                "    ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wat.city, '') = '' " +
                "     AND IFNULL(wat.area, '') = '' " +
                "    ) " +
                "    OR ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wuc.city, '') != '' " +
                "     AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "     AND IFNULL(wat.area, '') = '' " +
                "    ) " +
                "    OR ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wuc.city, '') != '' " +
                "     AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "     AND IFNULL(wuc.region, '') != '' " +
                "     AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "    ) " +
                "   ) " +
                "  ) " +
                " ) " +
                " GROUP BY " +
                "  wpc.productid " +
                ") wpc ON ws.productid = wpc.productid " +
                "WHERE " +
                " ws.userid = ? " +
                "AND ws.productid IN (" + sqlvo + ") " +
                "AND wp.`status` = 2 " +
                "AND ( " +
                " wp.iscontrol = 0 " +
                " OR ( " +
                "  wp.iscontrol = 1 " +
                "  AND wpc.productid IS NOT NULL " +
                " ) " +
                ") " +
                "ORDER BY " +
                " ws.create_time";
        return list(sql, new Object[]{userId, userId}, CartItem.class);
    }

    public List<CartItem> findOrderProducts(String code) {
        String sql = "SELECT wop.productid,wop.amount count,IFNULL(wpp.amount,0)- IFNULL(wpp.djamount,0)-IFNULL(wop.amount,0) amount from wb_order_product wop join wb_products_prices wpp on wop.productid=wpp.productid WHERE wop.orderid = (SELECT id from wb_order_list WHERE code=?) and wop.gift=0";
        return list(sql, code, CartItem.class);
    }

    public List<CartItem> findCartProducts(Map<String, Object> parms) {
        String sql = "SELECT " +
                " ws.id, " +
                " ws.productid, " +
                " ws.num count, " +
                " IFNULL(wpp.amount, 0) - IFNULL(wpp.djamount, 0) - IFNULL(ws.num, 0) amount " +
                "FROM " +
                " wb_shopcart ws " +
                "JOIN wb_products_prices wpp ON ws.productid = wpp.productid " +
                "WHERE " +
                " ws.userid = :userid " +
                "AND ws.productid IN (:ids)";
        return list(sql, parms, CartItem.class);
    }
    public List<JdataBean> getOrderList(int day) {
        String sql = "SELECT " +
                " l.id, " +
                " l.userid, " +
                " l.source, " +
                " l.get_integral " +
                "FROM " +
                " wb_order_list l " +
                "WHERE " +
                " l.integral_state = 1 " +
                "AND TIMESTAMPDIFF( " +
                " DAY, " +
                " from_unixtime(l.receipt_time), " +
                " now() " +
                ") > ? " +
                "AND l. STATUS = 11 " +
                "AND l.deltag = 0 " +
                "AND l.receipt_time IS NOT NULL";
        return querySql(sql, new Object[]{day});
    }
}
